package ejb.cbm.commons;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;

import org.apache.log4j.Logger;

import ejb.cbm.operatoria.session.ChequeSession;

public class JdbcHelper {
	
	public static String formatoyyyyMMdd = "yyyyMMdd";
	public static String formatoYYYYMMDD = "yyyymmdd";
	
	static Logger logger = Logger.getLogger(ChequeSession.class.getName());

	public static String toDate(String date, String format) {

		StringBuilder sb = new StringBuilder();

		if (date != null) {
			sb.append("TO_DATE('" + date + "','" + format + "')");
		} else {
			sb.append("TO_DATE(NULL,'" + format + "')");
		}

		return sb.toString();
	}
	
	public static String toDate(java.util.Date date, String format) {

		StringBuilder sb = new StringBuilder();
		String fecha = "";
		

		if (date != null) {
			fecha = new SimpleDateFormat(format).format(date);
			sb.append("TO_DATE('" + fecha + "','" + format + "')");
		} else {
			sb.append("TO_DATE(NULL,'" + format + "')");
		}

		return sb.toString();
	}

	public static String toDate(java.util.Date date) {

		StringBuilder sb = new StringBuilder();
		String fecha = "";
		

		if (date != null) {
			fecha = new SimpleDateFormat(formatoyyyyMMdd).format(date);
			sb.append("TO_DATE('" + fecha + "','" + formatoYYYYMMDD + "')");
		} else {
			sb.append("TO_DATE(NULL,'" + formatoYYYYMMDD + "')");
		}

		return sb.toString();
	}
	
	public static String to_char_yyyymmdd(java.util.Date date) {

		StringBuilder sb = new StringBuilder();
		String fecha = "";
		

		if (date != null) {
			fecha = new SimpleDateFormat(formatoyyyyMMdd).format(date);
			sb.append("'" + fecha + "'");
		} else {
			return null;
		}

		return sb.toString();
	}
	public static String toNumber(String number) {

		String aux = null;

		if (number == null) {
			aux = "";
		} else {
			aux = number.trim();
		}

		if (aux == null || aux.length() == 0) {
			return "NULL";
		} else {
			return aux.replaceAll(",", ".");
		}
	}

	public static String toNumber(long number) {

		return toNumber(String.valueOf(number));
	}

	public static String toNumber(int number) {
		return toNumber(String.valueOf(number));
	}

	public static String toNumber(double number) {
		return toNumber(String.valueOf(number));
	}

	public static String to_char(String cadena) {

		if (cadena == null || cadena.length() == 0) {
			return "NULL";
		}

		cadena = cadena.replaceAll("'", "''");
		// cadena = cadena.replaceAll("\"","chr(34)");
		// cadena = cadena.replaceAll("&" , "chr(38)");

		return "'" + cadena + "'";
	}
	
	public static String to_char(Long number) {

		if (number == null ) 
				return null;
		else 
			return number.toString();
	}

	public static void rollback(Connection con) {
		try {
			if (con != null && !con.isClosed()) {
				con.rollback();
			}
		} catch (SQLException e) {
			logger.error("ROLLBACK", e);
		}
	}

	public static void commit(Connection con) {

		try {

			if (con != null) {
				con.commit();
			}

		} catch (SQLException e) {
			logger.error("COMMIT", e);
		}

	}

	public static void close(Connection con, CallableStatement cs, PreparedStatement ps, Statement st, ResultSet rs) {
		try {
			if (cs != null) {
				cs.close();
			}

			if (ps != null) {
				ps.close();
			}

			if (st != null) {
				st.close();
			}

			if (rs != null) {
				rs.close();
			}

			if (con != null) {
				con.close();
			}

		} catch (SQLException e) {
			logger.error("Cerrando conexion", e);
		}
	}

	public static void close(Connection cnn, Statement stm, ResultSet rst) {
		try {
			if (rst != null) {
				rst.close();
			}
			if (stm != null) {
				stm.close();
			}
			if (cnn != null) {
				cnn.close();
			}
		} catch (SQLException e) {
			logger.error("Cerrando conexion", e);
		}
	}

	public static void close(Connection cnn, Statement stm) {
		close(cnn, stm, null);
	}

	public static void close(Connection con) {
		try {
			if (con != null) {
				con.close();
			}
		} catch (SQLException e) {
			logger.error("Cerrando conexion", e);
		}
	}
/*
	public static void rollback(SessionContext sessionContext) {
		try {
			if (sessionContext != null) {
				sessionContext.setRollbackOnly();
			}
		}catch(Exception e){
			logger.log("Error a realizar rollback", Level.FATAL,null, e);
			e.printStackTrace();logger.error(e);
		}
	}

	public static void commit(SessionContext sessionContext) {
		try {

			if (sessionContext != null) {
				sessionContext.getUserTransaction().commit();
			}

		}catch(SystemException e){
			logger.log("Error a realizar commit", Level.FATAL,null, e);
			e.printStackTrace();logger.error(e);
		}catch(HeuristicMixedException e){
			logger.log("Error a realizar commit", Level.FATAL,null, e);
			e.printStackTrace();logger.error(e);
		}catch(HeuristicRollbackException e){
			logger.log("Error a realizar commit", Level.FATAL,null, e);
			e.printStackTrace();logger.error(e);
		}catch(RollbackException e){
			logger.log("Error a realizar commit", Level.FATAL,null, e);
			e.printStackTrace();logger.error(e);
		}
	}
	*/
	public static String toLikeUpper(String parametro){
		if(parametro==null){return "";}
		return "'%"+parametro.toUpperCase().trim()+"%'";
	}
}
